﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace DataAccessService.Repository.MySQL
{
    public class MySQLExcuteRepository : BaseRepository
    {
        /// <summary>
        /// 执行sql，返回执行结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExcuteSQL(MySqlConnection conn, string sql)
        {
            try
            {
                MySqlCommand command = new MySqlCommand(sql, conn);
                command.CommandTimeout = 60000;
                int result = command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 执行查询sql，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> ExcuteQuerySQL<T>(MySqlConnection conn, string sql)
        {
            List<T> items = new List<T>();
            try
            {
                DataSet dataSet = new DataSet();

                MySqlCommand command = new MySqlCommand(sql, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                adapter.Fill(dataSet);

                items = ConvertToEntity<T>(dataSet);
                return items;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return items;
            }
        }

        /// <summary>
        /// 执行查询sql，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExcuteQuerySQL(MySqlConnection conn, string sql)
        {
            DataSet dataSet = new DataSet();
            try
            {

                MySqlCommand command = new MySqlCommand(sql, conn);
                MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                adapter.Fill(dataSet);                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            if (dataSet == null)
                dataSet = new DataSet();
            if (dataSet.Tables.Count == 0)
            {
                dataSet = new DataSet();

                DataTable table = new DataTable();
                table.Columns.Add("");

                DataRow row = table.NewRow();
                row[0] = 0;
                table.Rows.Add(row);

                dataSet.Tables.Add(table);
            }
            return dataSet;
        }

        /// <summary>
        /// 执行查询sql，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExcuteQuerySQL(MySqlConnection conn, MySqlTransaction tran, string sql)
        {
            DataSet dataSet = new DataSet();
            try
            {

                MySqlCommand command = new MySqlCommand(sql, conn, tran);
                MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                adapter.Fill(dataSet);                
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            if (dataSet == null)
                dataSet = new DataSet();
            if (dataSet.Tables.Count == 0)
            {
                dataSet = new DataSet();

                DataTable table = new DataTable();
                table.Columns.Add("");

                DataRow row = table.NewRow();
                row[0] = 0;
                table.Rows.Add(row);

                dataSet.Tables.Add(table);
            }
            return dataSet;
        }

        /// <summary>
        /// 执行sql，返回执行结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran">事务</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool ExcuteSQL(MySqlConnection conn, MySqlTransaction tran, string sql)
        {
            try
            {
                MySqlCommand command = new MySqlCommand(sql, conn, tran);
                int result = command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 执行查询sql，返回查询结果
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran">事务</param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> ExcuteQuerySQL<T>(MySqlConnection conn, MySqlTransaction tran, string sql)
        {
            List<T> items = new List<T>();
            try
            {
                DataSet dataSet = new DataSet();

                MySqlCommand command = new MySqlCommand(sql, conn, tran);
                MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                adapter.Fill(dataSet);

                if (dataSet.Tables.Count == 0)
                {
                    dataSet = new DataSet();

                    DataTable table = new DataTable();
                    table.Columns.Add("");

                    DataRow row = table.NewRow();
                    row[0] = 0;
                    table.Rows.Add(row);

                    dataSet.Tables.Add(table);
                }

                items = ConvertToEntity<T>(dataSet);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return items;
        }
    }
}
